package day13;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.junit.Test;

public class JDBCDemo {

	@Test
	public void testSelectAll() {
		try {
//		    1、加载驱动Class.forName("");  
			Class.forName("com.mysql.jdbc.Driver");
//			2、获得连接对象Connection
			Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2012?useUnicode=true&characterEncoding=UTF-8", "root", "1234");
//			3、写sql语句
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student`";
//			4、创建Statement(一艘船)
			Statement statement = connection.createStatement();
//			5、执行sql语句
//			     (1) 更新类（更改了表里面数据）：delete/update/insert     executeUpdate()
//			           返回值：int，表示你影响的行数
//			      (2)查询（没有改变表里面数据）:  select                              executeQuery()
//			           返回值：结果集ResultSet
			ResultSet resultSet = statement.executeQuery(sql);
			ArrayList<Student> list = new ArrayList<Student>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				// 没一次变量，就可以拿到数据库表中的一行数据，这一行数据就代表一个学生的信息
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			
			for (Student student : list) {
				System.out.println(student);
			}
			
//			6、关闭连接
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	@Test
	public void testPreparedStatement() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2012?useUnicode=true&characterEncoding=UTF-8", "root", "1234");
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student`";
			// 预编译
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			// 打印statement对象可以打印出执行的sql语句
			System.out.println(preparedStatement);
			ArrayList<Student> list = new ArrayList<Student>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			
			for (Student student : list) {
				System.out.println(student);
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭打开的所有连接：Connection、PreparedStatement、ResultSet
			// 先打开的后关闭
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}
	
	
	@Test
	public void testPreparedStatement11() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student`";
			// 预编译
			preparedStatement = connection.prepareStatement(sql);
			// 打印statement对象可以打印出执行的sql语句
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			ArrayList<Student> list = new ArrayList<Student>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		System.out.println("JDBCDemo.testPreparedStatement11()");
	}
	
	@Test
	public void testSelectLike() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String searchName = "李";
		try {
			connection = JDBCUtil.getConnection();
			// String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` WHERE `name` LIKE '%张%'";
			String sql = "SELECT `id`,`name`,`age`,`gender` FROM `student` WHERE `name` LIKE ? ";
			// 预编译
			preparedStatement = connection.prepareStatement(sql);
			// preparedStatement.setString(1, "%张%");
			preparedStatement.setString(1, "%"+searchName+"%");
			resultSet = preparedStatement.executeQuery();
			// 打印statement对象可以打印出执行的sql语句
			System.out.println(preparedStatement);
			ArrayList<Student> list = new ArrayList<Student>();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String gender = resultSet.getString("gender");
				
				Student student = new Student(id, name, age, gender);
				list.add(student);
			}
			
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
	}
	
	@Test
	public void testDelete() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `student` WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			// parameter:参数
			preparedStatement.setInt(1, 3);
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
	}
	
	@Test
	public void testInsert() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO `student`(`name`,`age`,`gender`) VALUES(?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "小赵");
			preparedStatement.setInt(2, 24);
			preparedStatement.setString(3, "女");
			int count = preparedStatement.executeUpdate();
			System.out.println(preparedStatement);
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
	}
	
	@Test
	public void testUpdate() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE `student` SET `name`=?,`age`=?,`gender`=? WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "小赵11");
			preparedStatement.setInt(2, 25);
			preparedStatement.setString(3, "男");
			preparedStatement.setInt(4, 9);
			int count = preparedStatement.executeUpdate();
			System.out.println(preparedStatement);
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
